﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using Microsoft.Office.Interop.Excel;
using Microsoft.Office;
using System.Web;
using System.Runtime.InteropServices;
using System.Diagnostics;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using FCModel;
using System.Reflection;

namespace MiscLib
{
    public class BomExcel
    {
        /// <summary>
        /// 生成KF Bom
        /// </summary>
        /// <param name="KAIFABOMList"></param>
        /// <param name="FilePath"></param>
        /// <param name="Response"></param>
        /// <param name="Server"></param>
        public static string OutputKFBomExcel(List<KFBom> KFBOMList, string modelPath, string FilePath)
        {
            string FullFileName = FilePath;
            GC.Collect();
            Application excel = new Application();
            //excel.Visible = true;
            _Workbook book = excel.Application.Workbooks.Add(FullFileName);
            _Workbook xBk = book;
            _Worksheet xSt;
            int sheetCount = KFBOMList.Count;
            int allSheetCount = book.Worksheets.Count;
            try
            {
                for (int i = 0; i < KFBOMList.Count; i++)
                {
                    KFBom bom = KFBOMList[i];
                    //xBk = book;  
                    //xSt = xBk.Worksheets[i + 1];
                    (excel.ActiveWorkbook.Sheets[i + 1] as Worksheet).Select(Type.Missing);
                    xSt = (_Worksheet)xBk.ActiveSheet;
                    //设置显示比例
                    excel.ActiveWindow.Zoom = 100;
                    xSt.Name = bom.dt.TableName;
                    excel.Cells[5, 1] = bom.title.PartNO;
                    excel.Cells[5, 9] = bom.title.FileVersion;
                    excel.Cells[5, 13] = bom.title.ProductName;
                    excel.Cells[5, 15] = bom.title.ComUseReplace;
                    excel.Cells[5, 16] = bom.title.ComUseReplaceValue;
                    excel.Cells[6, 1] = bom.title.ProductDesc;
                    excel.Cells[6, 13] = bom.title.Customer;
                    excel.Cells[6, 15] = bom.title.UpdateDate;
                    DataView KFBomdv = bom.dt.DefaultView;
                    int excelRowNum = KFBomdv.Table.Rows.Count;
                    int excelColNum = KFBomdv.Table.Columns.Count;
                    Range rg = xSt.Range[excel.Cells[8, 1], excel.Cells[7 + excelRowNum, excelColNum]];
                    object[,] objData = new Object[excelRowNum, excelColNum];
                    for (int k = 0; k < excelRowNum; k++)
                    {
                        for (int j = 0; j < excelColNum; j++)
                        {
                            objData[k, j] = KFBomdv[k][j];
                        }
                    }
                    rg.Value2 = objData;

                    excel.Cells[8 + excelRowNum, 1] = bom.BomVersion;

                    List<int> mainPartRowNum = new List<int>();
                    for (int m = 0; m < KFBomdv.Table.Rows.Count; m++)
                    {
                        if (string.IsNullOrWhiteSpace(KFBomdv.Table.Rows[m][2].ToString())) 
                        {
                            mainPartRowNum.Add(m);
                        }
                    }
                    foreach (int row in mainPartRowNum)//变绿
                    {
                        rg = xSt.Range[excel.Cells[row + 8, 1], excel.Cells[row + 8, excelColNum]];
                        //设置单元格背景颜色
                        rg.Cells.Interior.Pattern = Constants.xlSolid;
                        rg.Cells.Interior.PatternColorIndex = Constants.xlAutomatic;
                        rg.Cells.Interior.Color = 10092441;
                        rg.Cells.Interior.TintAndShade = 0;
                        rg.Cells.Interior.PatternTintAndShade = 0;
                    }
                    rg = xSt.Range[excel.Cells[8, 1], excel.Cells[excelRowNum + 7, excelColNum]];
                    rg.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                    KFBomdv = null;
                }

                //删除其他sheet   (xlsApp.ActiveWorkbook.Sheets[1] as Worksheet).Delete();
                //while ((excel.ActiveWorkbook.Sheets[sheetCount + 1]) != null) 
                //{
                //    (excel.ActiveWorkbook.Sheets[sheetCount + 1] as Worksheet).Delete();
                //}
                for (int f = 0; f < allSheetCount - sheetCount; f++)
                {
                    excel.DisplayAlerts = false;
                    (excel.ActiveWorkbook.Sheets[sheetCount + 1] as Worksheet).Delete();
                }
                xBk.SaveCopyAs(FullFileName);
                xBk.Close(false, null, null);
                excel.Quit();
            }
            finally
            {
                try
                {
                    Kill_Excel(excel);
                }
                catch { ; }
                GC.Collect();
            }
            #region download excel
            return FullFileName;
            //DownLoadFile(FullFileName, Response, Server);
            #endregion
        }

        /// <summary>
        /// 生成SAP Bom
        /// </summary>
        /// <param name="SAPBOMdv"></param>
        /// <param name="FilePath"></param>
        /// <param name="Response"></param>
        /// <param name="Server"></param>
        public static string OutputSapBomExcel(DataView SAPBOMdv,string FilePath)
        {
            int excelRowNum = SAPBOMdv.Count;
            int excelColNum = SAPBOMdv.Table.Columns.Count; ;
            string FullFileName = FilePath;
            GC.Collect();
            Application excel = new Application();
            //excel.Visible = true;
            Workbook book = excel.Application.Workbooks.Add(FullFileName);
            _Workbook xBk;
            _Worksheet xSt;
            try
            {
                //WriteLog(logPath, tp.TrainingPlanID + "进入生成excel3");
                xBk = book;
                xSt = (_Worksheet)xBk.ActiveSheet;
                //设置显示比例
                excel.ActiveWindow.Zoom = 100;
                //设置字体
                //region 表头
                Range rg = xSt.Range[excel.Cells[2, 1], excel.Cells[1 + excelRowNum, excelColNum]];

                object[,] objData1 = new Object[excelRowNum, excelColNum];

                for (int i = 0; i < excelRowNum; i++)
                {
                    for (int j = 0; j < excelColNum; j++)
                    {
                        objData1[i, j] = SAPBOMdv[i][j];
                    }
                }
                rg.Value2 = objData1;

                //excel.Cells[excelRowNum + 2, 1] = "Form NO.：FM-009-288 Rev.01";
                //rg = xSt.Range[excel.Cells[6, 1], excel.Cells[excelRowNum + 6, excelColNum + 2]];
                //rg.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                
                xBk.SaveCopyAs(FullFileName);
                //WriteLog(logPath, tp.TrainingPlanID + "进入生成excel5");
                SAPBOMdv = null;
                xBk.Close(false, null, null);
                excel.Quit();
                //WriteLog(logPath, tp.TrainingPlanID + "进入生成excel6");
            }
            finally
            {
                try
                {
                    Kill_Excel(excel);
                }
                catch { ; }
                GC.Collect();
            }
            #region download excel
            //
            return FullFileName;
            //DownLoadFile(FullFileName, Response, Server);
            #endregion
        }

        public static void DownLoadFile(string FullFileName, HttpResponse Response, HttpServerUtility Server)
        {
            System.IO.FileInfo file = new System.IO.FileInfo(FullFileName);
            Response.Clear();
            Response.Charset = "UTF-8";
            Response.ContentEncoding = System.Text.Encoding.UTF8;
            //Add header, give a default file name for "File Download/Store as"
            Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
            //Add header, set file size to enable browser display download progress
            Response.AddHeader("Content-Length", file.Length.ToString());
            //Set the return string is unavailable reading for client, and must be downloaded
            Response.ContentType = "application/ms-excel";
            //Send file string to client 
            Response.WriteFile(file.FullName);
            //Stop execute  
            HttpContext.Current.ApplicationInstance.CompleteRequest();
        }

        [DllImport("User32.dll", CharSet = CharSet.Auto)]
        private static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);
        private static void Kill_Excel(Microsoft.Office.Interop.Excel.Application excel)
        {
            IntPtr t = new IntPtr(excel.Hwnd);   //得到这个句柄，具体作用是得到这块内存入口 
            int k = 0;
            GetWindowThreadProcessId(t, out k);   //得到本进程唯一标志k
            if (k == 0)    //由于权限不足 获取不到PID时
            {
                System.Diagnostics.Process[] p = System.Diagnostics.Process.GetProcessesByName("Excel");
                foreach (Process process in p)
                {
                    process.Kill();
                }
            }
            else
            {
                System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);   //得到对进程k的引用
                p.Kill();     //关闭进程k
            }
        }
    
        //public static void OutputKFBomExcel(List<KFBom> KFBOMList, string modelPath, string FilePath, HttpResponse Response, HttpServerUtility Server)
        //{
        //    string FullFileName = FilePath;

        //    GC.Collect();
        //    Application excel = new Application();
        //    excel.Visible = true;
        //    Workbook xBk;
        //    Worksheet xSt;
        //    xBk = excel.Workbooks.Add();
        //    //Microsoft.Office.Interop.Excel.Workbook book = excel.Workbooks.Open(FullFileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
        //    try
        //    {
        //        for (int i = 0; i < KFBOMList.Count; i++)
        //        {
        //            KFBom bom = KFBOMList[i];
        //            DataView KFBomdv = bom.dt.DefaultView;
        //            xSt = (Worksheet)xBk.Worksheets.Add();
        //            //xSt = (Worksheet)xBk.ActiveSheet();
        //            xSt.Name = KFBomdv.Table.TableName;

        //            //Worksheet sx1 = xBk.Worksheets.Add();
        //            copyExcelSheetByModel(modelPath, xBk, "");
        //            //copy(modelPath, xSt);
        //            //设置显示比例
        //            excel.ActiveWindow.Zoom = 100;

        //            //((Microsoft.Office.Interop.Excel.Worksheet)excel.Sheets[i]).Name = "5";

        //            //设置字体
        //            //region 表头
        //            excel.Cells[5, 1] = bom.title.PartNO;
        //            excel.Cells[5, 9] = bom.title.FileVersion;
        //            excel.Cells[5, 13] = bom.title.ProductName;
        //            excel.Cells[5, 15] = bom.title.ComUseReplace;
        //            excel.Cells[5, 16] = bom.title.ComUseReplaceValue;
        //            excel.Cells[6, 1] = bom.title.ProductDesc;
        //            excel.Cells[6, 13] = bom.title.Customer;
        //            excel.Cells[6, 15] = bom.title.UpdateDate;

        //            int excelRowNum = KFBomdv.Table.Rows.Count;
        //            int excelColNum = KFBomdv.Table.Columns.Count;
        //            Range rg = xSt.Range[excel.Cells[8, 1], excel.Cells[7 + excelRowNum, excelColNum]];
        //            object[,] objData = new Object[excelRowNum, excelColNum];
        //            for (int k = 0; k < excelRowNum; k++)
        //            {
        //                for (int j = 0; j < excelColNum; j++)
        //                {
        //                    objData[k, j] = KFBomdv[k][j];
        //                }
        //            }
        //            rg.Value2 = objData;
        //            xBk.SaveCopyAs(FullFileName);
        //            KFBomdv = null;
        //            xBk.Close(false, null, null);
        //        }
        //        excel.Quit();
        //    }
        //    finally
        //    {
        //        try
        //        {
        //            Kill_Excel(excel);
        //        }
        //        catch { ; }
        //        GC.Collect();
        //    }
        //    #region download excel
        //    DownLoadFile(FullFileName, Response, Server);
        //    #endregion
        //}

    }
}
